package com.fly.common.manager;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import lombok.extern.slf4j.Slf4j;

/**
 * 
 * 树状数据初始化
 * 
 * @author 00fly
 * @version [版本号, 2022年5月6日]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Slf4j
@Service
public class InterfaceDataManager
{
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    /**
     * 处理数据并入库
     * 
     * @throws IOException
     * 
     * @see [类、类#方法、类#成员]
     */
    public void initData()
        throws IOException
    {
        Resource resource = new ClassPathResource("data/data.xlsx");
        File file = resource.getFile();
        try (XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)))
        {
            // 解析树状实体
            List<TreeData> list = new ArrayList<>();
            XSSFSheet sheet = wb.getSheetAt(0);
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++)
            {
                XSSFRow row = sheet.getRow(rowIndex);
                if (row == null)
                {
                    continue;
                }
                TreeData data = new TreeData();
                // 组装主题、子主题、数据、表4级树状联动数据
                data.setName1(readCell(row, 0)).setId1(DigestUtils.md5Hex(data.getName1()));
                data.setName2(readCell(row, 1)).setId2(DigestUtils.md5Hex(data.getName2()));
                data.setName3(readCell(row, 2)).setId3(DigestUtils.md5Hex(data.getName3()));
                data.setName4(readCell(row, 3)).setId4(DigestUtils.md5Hex(data.getName4())).setTableName(readCell(row, 4));
                list.add(data);
            }
            
            // 入库处理,先删除
            jdbcTemplate.execute("delete from t_structure");
            
            // 再批量插入
            Set<String> ids = new HashSet<>();
            String sql = "INSERT INTO t_structure(`id`, `name`, `english_name`, `parent_id`) VALUES (?, ?, ?, ?)";
            List<Object[]> batchArgs = new ArrayList<>();
            list.forEach(it -> {
                if (!ids.contains(it.getId1()))
                {
                    batchArgs.add(new String[] {it.getId1(), it.getName1(), null, "0"});
                    ids.add(it.getId1());
                }
                if (!ids.contains(it.getId2()))
                {
                    batchArgs.add(new String[] {it.getId2(), it.getName2(), null, it.getId1()});
                    ids.add(it.getId2());
                }
                if (!ids.contains(it.getId3()))
                {
                    batchArgs.add(new String[] {it.getId3(), it.getName3(), null, it.getId2()});
                    ids.add(it.getId3());
                }
                if (!ids.contains(it.getId4()))
                {
                    batchArgs.add(new String[] {it.getId4(), it.getName4(), it.getTableName(), it.getId3()});
                    ids.add(it.getId4());
                }
            });
            jdbcTemplate.batchUpdate(sql, batchArgs);
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 读取row行第index列表格文本内容
     * 
     * @param row
     * @param index从0开始
     * @return
     * @see [类、类#方法、类#成员]
     */
    private String readCell(XSSFRow row, int index)
    {
        XSSFCell cell = row.getCell(index);
        if (cell != null)
        {
            cell.setCellType(CellType.STRING);
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        }
        return "";
    }
}
